public class NewsManager
{
    public static NewsList GetList()
    {
        NewsList tempList = null;

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        string mySql = "select * from News  ";
        SqlCommand myCommand = new SqlCommand(mySql, myConnection);

        myConnection.Open();
        SqlDataReader myReader = null;

        myReader = myCommand.ExecuteReader();

        if (myReader.HasRows)
        {
            tempList = new NewsList();

            while (myReader.Read())
                tempList.Add(FillDataRecord(myReader));
        }

        myReader.Close();
        myConnection.Close();

        return tempList;

    }

    public static News GetItem(int id)
    {
        News myNews = new News();

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        string SQL = "SELECT * FROM [News]  WHERE [ID] = @ID";

        SqlCommand myCommand = new SqlCommand(SQL , myConnection);
   
        myCommand.Parameters.AddWithValue("@id", id);

        myConnection.Open();

        SqlDataReader myReader = myCommand.ExecuteReader();

        if (myReader.Read())
        {
            myNews = FillDataRecord(myReader);
        }

        myReader.Close();
        myConnection.Close();

        return myNews;
    }



    public static int Save(News myNews)
    {
        int result = 0;

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        SqlCommand myCommand;
        
        if (myNews.Id == -1)
        {
            String SQL = "INSERT INTO [News] ([Visible], [Title], [Date], [Content], [ImageUrl], [DisplayOrder]) VALUES (@Visible, @Title, @Date, @Content, @ImageUrl, @DisplayOrder)";
            myCommand = new SqlCommand(SQL, myConnection);

        }
        else
        {
            String SQL = "UPDATE [News] SET [Visible] = @Visible, [Title] = @Title, [Date] = @Date, [Content] = @Content, [ImageUrl] = @ImageUrl, [DisplayOrder] = @DisplayOrder WHERE [ID] = @ID";
            myCommand = new SqlCommand(SQL, myConnection);
            myCommand.Parameters.AddWithValue("@id", myNews.Id);
        }
        
        myCommand.Parameters.AddWithValue("@visible", myNews.Visible);
        myCommand.Parameters.AddWithValue("@title", myNews.Title);
        myCommand.Parameters.AddWithValue("@date", System.DateTime.Now);
        myCommand.Parameters.AddWithValue("@content", myNews.Content);
        myCommand.Parameters.AddWithValue("@imageUrl", myNews.ImageUrl);
        myCommand.Parameters.AddWithValue("@displayOrder", myNews.DisplayOrder);

        myConnection.Open();

        result = myCommand.ExecuteNonQuery();

        myConnection.Close();

        return result;

    }

    public static bool Delete(News myNews)
    {
        int id = myNews.Id;
        int result = 0;

        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

        string SQL = "DELETE FROM [News] WHERE [ID] = @ID";

        SqlCommand myCommand = new SqlCommand(SQL, myConnection);
        myCommand.Parameters.AddWithValue("@id", id);

        myConnection.Open();

        result = myCommand.ExecuteNonQuery();

        myConnection.Close();

        return result > 0;
    }

    private static News FillDataRecord(SqlDataReader  myReader)
    {
        News myNews = new News();

        myNews.Id =(int) myReader ["Id"]; 

        myNews.Visible =(bool) myReader["Visible"];
        myNews.Title = myReader["Title"].ToString ();
        myNews.Date  = (DateTime )myReader["Date"];
        
        myNews.Content =myReader["Content"].ToString ();
        myNews.ImageUrl =myReader["ImageUrl"].ToString ();

        myNews.DisplayOrder =(int) myReader ["DisplayOrder"];


        return myNews;
    }
}
